﻿/// <summary>
/// Copyright (C) 2007-2010 
/// 数据访问基础类(基于SQLServer)
/// </summary>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System.Reflection;
using System.Data.SqlClient;

using Job.Core.Module;

namespace Job.Core
{
    /// <summary>
    /// 泛型类 TType为类型参数 where子句用来指定类型约束[接口约束、基类约束、构造函数约束]
    /// 基类约束：指出某个类型必须将指定的类作为基类（或者就是该类本身），才能用作该泛型类型的类型参数。这样的约束一经使用，就必须出现在该类型参数的所有其他约束之前。
    /// 构造函数约束：使用new运算符创建类型参数的实例；但类型参数为此必须受构造函数约束。new()约束可以让编译器知道：提供的任何类型参数都必须具有可访问的无参数构造函数。
    /// </summary>
    /// <typeparam name="TType"></typeparam>
    public class BaseDAL<TKey, TType> : IBaseDAL<TKey, TType>
        where TType : Business<TKey, TType>, new()
        where TKey : IComparable
    {
        private string _strPrimaryKey = "";
        private string _strTableName = "";
        private TType t = Business<TKey, TType>.SModel;
        

        #region 基本属性方法
        /// <summary>
        /// 要连接的数据库名
        /// </summary>
        public string strConName
        {
            get { return BaseAccessHelper.getConnName(t.DataBaseName); }
        }

        /// <summary>
        /// 主键字段名
        /// </summary>
        public string strPrimaryKey
        {
            get
            {
                if (_strPrimaryKey == "")
                    _strPrimaryKey = t.PrimaryKey;
                return _strPrimaryKey;
            }
            set { _strPrimaryKey = value; }
        }

        /// <summary>
        /// 数据表名
        /// </summary>
        public string strTableName
        {
            get
            {
                if (_strTableName == "")
                    _strTableName = "[" + typeof(TType).Name + "]";
                return _strTableName;
            }
            set { _strTableName = value; }
        }
        #endregion

        #region GetCount
        /// <summary>
        /// 获得记录数
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual int GetCount(string strWhere)
        {
            string strSQL = string.Format("SELECT COUNT(0) FROM {0}", this.strTableName);
            if (!string.IsNullOrEmpty(strWhere))
                strSQL += string.Format(" WHERE {0}", strWhere);
            Database db = DatabaseFactory.CreateDatabase(strConName);
            DbCommand cmd = db.GetSqlStringCommand(strSQL);
            return Convert.ToInt32(db.ExecuteScalar(cmd));
        }
        #endregion

        #region Exits
        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <param name="intID">主键ID</param>
        /// <returns></returns>
        public virtual bool Exists(int intID)
        {
            return Exists(string.Format("{0} = {1}", strPrimaryKey, intID));
        }

        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual bool Exists(string strWhere)
        {
            return Exists(strWhere, strTableName);
        }

        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="sTableName">表名</param>
        internal bool Exists(string strWhere, string strTableName)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("SELECT COUNT(1) FROM {0}", strTableName);
            if (!string.IsNullOrEmpty(strWhere))
                sbSql.AppendFormat(" WHERE {0}", strWhere);

            Database db = DatabaseFactory.CreateDatabase(strConName);
            DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString());
            object obj = db.ExecuteScalar(cmd);

            int cmdresult = 0;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                cmdresult = 0;
            else
                cmdresult = int.Parse(obj.ToString());
            if (cmdresult == 0)
                return false;
            else
                return true;
        }
        #endregion

        #region Add
        /// <summary>
        /// 批量添加记录
        /// </summary>
        /// <param name="list">存储实体类的List</param>
        public virtual int Add(List<TType> list)
        {
            return AddList(list);
        }

        /// <summary>
        /// 批量添加记录
        /// </summary>
        /// <param name="dt">包含记录的DataTable</param>
        public virtual int Add(DataTable dt)
        {
            List<TType> list = new List<TType>();
            foreach (DataRowView drv in dt.DefaultView)
            {
                list.Add(GetModel(drv));
            }
            return AddList(list);
        }

        /// <summary>
        /// 批量添加记录
        /// </summary>
        /// <param name="dr">包含记录的DataReader</param>
        public virtual int Add(IDataReader dr)
        {
            List<TType> list = new List<TType>();
            while (dr.Read())
            {
                list.Add(GetModel(dr));
            }
            return AddList(list);
        }

        /// <summary>
        /// 添加单条记录，返回当前插入的这条记录的ID
        /// </summary>
        /// <param name="model">实体层某实体的实例</param>
        public virtual int Add(TType model)
        {
            //PropertyInfo类：发现属性 (Property) 的属性 (Attribute) 并提供对属性 (Property) 元数据的访问。
            //PropertyInfo[] pis = t.GetPropertyInfo();
            PropertyInfo[] pis = t.GetPropertyInfo();
            SqlParameter[] parameters = new SqlParameter[pis.Length - 1];

            StringBuilder sbCloumnName = new StringBuilder();
            StringBuilder sbParameter = new StringBuilder();

            List<DataBaseParameter> listParam = new List<DataBaseParameter>();

            for (int i = 0; i <= parameters.Length; i++)
            {
                if (t.HasIdentityPK && t.PrimaryKey == pis[i].Name)
                    continue;
                if (!pis[i].CanWrite)
                    continue;

                sbCloumnName.AppendFormat("[{0}],", pis[i].Name);
                sbParameter.AppendFormat("@{0},", pis[i].Name);

                DataBaseParameter dbpm = new DataBaseParameter();
                dbpm.ParamName = pis[i].Name;
                dbpm.ParamDbType = BaseAccessHelper.GetDbType(pis[i].PropertyType);
                dbpm.ParamValue = pis[i].GetValue(model, null);
                listParam.Add(dbpm);
            }

            sbCloumnName = sbCloumnName.Replace(",", "", sbCloumnName.Length - 1, 1);
            sbParameter = sbParameter.Replace(",", "", sbParameter.Length - 1, 1);

            string strCommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2});SELECT IDENT_CURRENT('{0}')", strTableName, sbCloumnName, sbParameter);

            Database db = DatabaseFactory.CreateDatabase(strConName);
            DbCommand cmd = db.GetSqlStringCommand(strCommandText);

            foreach (DataBaseParameter dbpm in listParam)
            {
                db.AddInParameter(cmd, dbpm.ParamName, dbpm.ParamDbType, dbpm.ParamValue);
            }

            object obj = db.ExecuteScalar(cmd);

            return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 批量添加记录 
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        internal int AddList(List<TType> list)
        {
            int intCount = 0;
            SqlConnection conn = new SqlConnection(BaseAccessHelper.GetSqlConnectionString(t.DataBaseName));
            conn.Open();
            foreach (TType model in list)
            {
                //PropertyInfo类：发现属性 (Property) 的属性 (Attribute) 并提供对属性 (Property) 元数据的访问。
                //PropertyInfo[] pis = t.GetPropertyInfo();
                PropertyInfo[] pis = t.GetPropertyInfo();
                SqlParameter[] parameters = new SqlParameter[pis.Length - 1];

                StringBuilder sbCloumnName = new StringBuilder();
                StringBuilder sbParameter = new StringBuilder();

                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandType = CommandType.Text;

                for (int i = 0; i <= parameters.Length; i++)
                {
                    if (t.HasIdentityPK && t.PrimaryKey == pis[i].Name)
                        continue;
                    if (!pis[i].CanWrite)
                        continue;

                    sbCloumnName.AppendFormat("[{0}],", pis[i].Name);
                    string strParameterName = string.Format("@{0}", pis[i].Name);
                    sbParameter.AppendFormat("{0},", strParameterName);

                    command.Parameters.Add(new SqlParameter(strParameterName, BaseAccessHelper.GetDbType(pis[i].PropertyType)));
                    command.Parameters[strParameterName].Value = pis[i].GetValue(model, null);
                }

                sbCloumnName = sbCloumnName.Replace(",", "", sbCloumnName.Length - 1, 1);
                sbParameter = sbParameter.Replace(",", "", sbParameter.Length - 1, 1);

                command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", strTableName, sbCloumnName, sbParameter);

                if (command.ExecuteNonQuery() > 0)
                    intCount++;
            }
            conn.Close();
            return intCount;
        }
        #endregion Add

        #region Update
        /// <summary>
        /// 批量更新记录
        /// </summary>
        /// <param name="dt">存储需要更新的记录的DataTable</param>
        public virtual int Update(DataTable dt)
        {
            return Update(dt, "");
        }

        /// <summary>
        /// 批量更新记录
        /// </summary>
        /// <param name="dt">存储需要更新的记录的DataTable</param>
        /// <param name="strColumnList">按逗号隔开的需要被更新的字段</param>
        /// <returns></returns>
        public virtual int Update(DataTable dt, string strColumnList)
        {
            List<TType> list = new List<TType>();
            foreach (DataRowView drv in dt.DefaultView)
            {
                list.Add(GetModel(drv));
            }
            return Update(list, strColumnList);
        }

        /// <summary>
        /// 批量更新记录
        /// </summary>
        /// <param name="list">存储需要更新的记录的List</param>
        public virtual int Update(List<TType> list)
        {
            return Update(list, "");
        }

        /// <summary>
        /// 批量更新记录
        /// </summary>
        /// <param name="list">存储需要更新的记录的List</param>
        /// <param name="strColList">按逗号隔开的需要被更新的字段</param>
        /// <returns></returns>
        public virtual int Update(List<TType> list, string strColList)
        {
            return UpdateList(list, strColList);
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">需要更新到数据库的实体类</param>
        public virtual bool Update(TType model)
        {
            return Update(model, "");
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">需要更新到数据库的实体类</param>
        /// <param name="strColumnList">需要更新的字段</param>
        public virtual bool Update(TType model, string strColumnList)
        {
            PropertyInfo[] pis = t.GetPropertyInfo();
            StringBuilder sbSql = new StringBuilder();
            List<DataBaseParameter> listParam = new List<DataBaseParameter>();
            DataBaseParameter dbpm = null;
            if (string.IsNullOrEmpty(strColumnList))//更新除主键字段外的所有字段
            {
                for (int i = 0; i < pis.Length; i++)
                {
                    if (!pis[i].CanWrite)
                        continue;

                    if (pis[i].Name != strPrimaryKey)
                        sbSql.AppendFormat("[{0}] = @{0},", pis[i].Name);

                    dbpm = new DataBaseParameter();
                    dbpm.ParamName = pis[i].Name;
                    dbpm.ParamDbType = BaseAccessHelper.GetDbType(pis[i].PropertyType);
                    dbpm.ParamValue = pis[i].GetValue(model, null);
                    listParam.Add(dbpm);
                }
            }
            else
            {
                string[] strArr = strColumnList.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < strArr.Length; i++)
                {
                    string strVal = strArr[i].ToLower().Trim();
                    if (strVal != strPrimaryKey.ToLower())
                    {
                        foreach (PropertyInfo pi in pis)
                        {
                            if (!pi.CanWrite)
                                continue;

                            if (pi.Name.ToLower() == strVal)
                            {
                                sbSql.AppendFormat("[{0}] = @{0},", pi.Name);
                                dbpm = new DataBaseParameter();
                                dbpm.ParamName = pi.Name;
                                dbpm.ParamDbType = BaseAccessHelper.GetDbType(pi.PropertyType);
                                dbpm.ParamValue = pi.GetValue(model, null);
                                listParam.Add(dbpm);
                                break;
                            }
                        }
                    }
                }
                //将主键字段及其值加入到参数列表中
                foreach (PropertyInfo pi in pis)
                {
                    if (pi.Name == strPrimaryKey)
                    {
                        dbpm = new DataBaseParameter();
                        dbpm.ParamName = pi.Name;
                        dbpm.ParamDbType = DbType.Int32;
                        dbpm.ParamValue = pi.GetValue(model, null);
                        listParam.Add(dbpm);
                        break;
                    }
                }
            }
            sbSql = sbSql.Replace(",", "", sbSql.Length - 1, 1);
            string strCommandText = string.Format("UPDATE {0} SET {1} WHERE {2} = @{2}", strTableName, sbSql, strPrimaryKey);
            Database db = DatabaseFactory.CreateDatabase(strConName);
            DbCommand cmd = db.GetSqlStringCommand(strCommandText);
            foreach (DataBaseParameter dbp in listParam)
            {
                db.AddInParameter(cmd, dbp.ParamName, dbp.ParamDbType, dbp.ParamValue);
            }
            return db.ExecuteNonQuery(cmd) > 0 ? true : false;
        }

        /// <summary>
        /// 批量更新记录
        /// </summary>
        /// <param name="list">存储需要更新的记录的List</param>
        /// <param name="strColumnList">按逗号隔开的需要被更新的字段</param>
        /// <returns></returns>
        internal int UpdateList(List<TType> list, string strColumnList)
        {
            int intCount = 0;
            SqlConnection conn = new SqlConnection(BaseAccessHelper.GetSqlConnectionString(t.DataBaseName));
            conn.Open();
            foreach (TType model in list)
            {
                if (model == null)
                    continue;

                //PropertyInfo[] pis = t.GetPropertyInfo();
                PropertyInfo[] pis = t.GetPropertyInfo();
                StringBuilder sbSql = new StringBuilder();
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandType = CommandType.Text;
                if (string.IsNullOrEmpty(strColumnList))//更新除主键字段外的所有字段
                {
                    for (int i = 0; i < pis.Length; i++)
                    {
                        if (!pis[i].CanWrite)
                            continue;

                        string strParameterName = string.Format("@{0}", pis[i].Name);
                        if (pis[i].Name != strPrimaryKey)
                            sbSql.AppendFormat("[{0}] = {1},", pis[i].Name, strParameterName);
                        command.Parameters.Add(new SqlParameter(strParameterName, BaseAccessHelper.GetDbType(pis[i].PropertyType)));
                        command.Parameters[strParameterName].Value = pis[i].GetValue(model, null);
                    }
                }
                else
                {
                    string[] strArr = strColumnList.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                    for (int i = 0; i < strArr.Length; i++)
                    {
                        string strVal = strArr[i].ToLower().Trim();
                        if (strVal != strPrimaryKey.ToLower())
                        {
                            foreach (PropertyInfo pi in pis)
                            {
                                if (!pi.CanWrite)
                                    continue;

                                if (pi.Name.ToLower() == strVal)
                                {
                                    string strParameterName = string.Format("@{0}", pi.Name);
                                    sbSql.AppendFormat("[{0}] = {1},", pi.Name, strParameterName);
                                    command.Parameters.Add(new SqlParameter(strParameterName, BaseAccessHelper.GetDbType(pi.PropertyType)));
                                    command.Parameters[strParameterName].Value = pi.GetValue(model, null);
                                    break;
                                }
                            }
                        }
                    }
                    //将主键字段及其值加入到参数列表中
                    foreach (PropertyInfo pi in pis)
                    {
                        string strParameterName = string.Format("@{0}", strPrimaryKey);
                        if (pi.Name == strPrimaryKey)
                        {
                            command.Parameters.Add(new SqlParameter(strParameterName, BaseAccessHelper.GetDbType(pi.PropertyType)));
                            command.Parameters[strParameterName].Value = pi.GetValue(model, null);
                            break;
                        }
                    }
                }
                sbSql = sbSql.Replace(",", "", sbSql.Length - 1, 1);
                command.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2} = @{2}", strTableName, sbSql, strPrimaryKey);
                if (command.ExecuteNonQuery() > 0)
                    intCount++;
            }
            conn.Close();
            return intCount;
        }


        public virtual bool Update(Dictionary<string, object> dict, string strColumnList)
        {
            return Update(dict, strColumnList, string.Empty);
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">需要更新到数据库的实体类</param>
        /// <param name="strColumnList">需要更新的字段</param>
        public virtual bool Update(Dictionary<string, object> dict, string strColumnList ,string strWhere)
        {            
            PropertyInfo[] pis = t.GetPropertyInfo();
            StringBuilder sbSql = new StringBuilder();
            List<DataBaseParameter> listParam = new List<DataBaseParameter>();
            DataBaseParameter dbpm = null;
            if (string.IsNullOrEmpty(strColumnList))//更新除主键字段外的所有字段
            {
                for (int i = 0; i < pis.Length; i++)
                {
                    string strPisName = pis[i].Name;
                    if (!pis[i].CanWrite || !dict.ContainsKey(strPisName))
                        continue;

                    if (strPisName != strPrimaryKey)
                        sbSql.AppendFormat("[{0}] = @{0},", strPisName);

                    dbpm = new DataBaseParameter();
                    dbpm.ParamName = strPisName;
                    dbpm.ParamDbType = BaseAccessHelper.GetDbType(pis[i].PropertyType);
                    dbpm.ParamValue = dict[strPisName];
                    listParam.Add(dbpm);
                }
            }
            else
            {
                string[] strArr = strColumnList.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < strArr.Length; i++)
                {
                    string strVal = strArr[i].ToLower().Trim();
                    string strPisName = pis[i].Name;
                    if (strVal != strPrimaryKey.ToLower())
                    {
                        foreach (PropertyInfo pi in pis)
                        {
                            if (!pi.CanWrite || !dict.ContainsKey(strPisName) || pi.Name.ToLower() != strVal)
                                continue;

                            sbSql.AppendFormat("[{0}] = @{0},", strPisName);
                            dbpm = new DataBaseParameter();
                            dbpm.ParamName = strPisName;
                            dbpm.ParamDbType = BaseAccessHelper.GetDbType(pi.PropertyType);
                            dbpm.ParamValue = dict[strPisName];
                            listParam.Add(dbpm);
                            break;
                        }
                    }
                }

            }
            sbSql = sbSql.Replace(",", "", sbSql.Length - 1, 1);
            string strCommandText = string.Empty;
            if (string.IsNullOrEmpty(strWhere))
            {
                strCommandText = string.Format("UPDATE {0} SET {1} WHERE {2} = @{2}", strTableName, sbSql, strPrimaryKey);
            }
            else
            {
                strCommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", strTableName, sbSql, strWhere);
            }

            Database db = DatabaseFactory.CreateDatabase(strConName);
            DbCommand cmd = db.GetSqlStringCommand(strCommandText);
            foreach (DataBaseParameter dbp in listParam)
            {
                db.AddInParameter(cmd, dbp.ParamName, dbp.ParamDbType, dbp.ParamValue);
            }
            return db.ExecuteNonQuery(cmd) > 0 ? true : false;
        }
        #endregion Update

        #region Delete
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="intID">记录ID</param>
        public virtual int Delete(int intID)
        {
            return Delete(new List<int>() { intID });
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual int Delete(string strWhere)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("DELETE {0}", strTableName);
            if (!string.IsNullOrEmpty(strWhere))
                sbSql.AppendFormat(" WHERE {0}", strWhere);

            Database db = DatabaseFactory.CreateDatabase(strConName);
            DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString());
            return db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 批量删除记录
        /// </summary>
        /// <param name="list">主键ID的集合</param>
        /// <returns></returns>
        public virtual int Delete(List<int> list)
        {
            int intCount = 0;
            SqlConnection conn = new SqlConnection(BaseAccessHelper.GetSqlConnectionString(t.DataBaseName));
            conn.Open();
            foreach (var obj in list)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = string.Format("DELETE {0} WHERE {1} = {2}", strTableName, strPrimaryKey, obj);
                command.CommandType = CommandType.Text;
                if (command.ExecuteNonQuery() > 0)
                    intCount++;
            }
            conn.Close();
            return intCount;
        }
        #endregion

        #region GetModel
        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="intID">主键ID的值</param>
        public virtual TType GetModel(int intID)
        {
            return GetModel(intID, "*");
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="intID">主键ID的值</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        public virtual TType GetModel(int intID, string strColumnList)
        {
            string strWhere = string.Format("[{0}] = {1}", strPrimaryKey, intID);
            return GetModel(strWhere, strColumnList, "", false);
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual TType GetModel(string strWhere)
        {
            return GetModel(strWhere, "*");
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        public virtual TType GetModel(string strWhere, string strColumnList)
        {
            return GetModel(strWhere, strColumnList, true);
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="blOrderType">排序类型 true=降序 false=升序</param>
        /// <returns></returns>
        public virtual TType GetModel(string strWhere, string strColumnList, bool blOrderType)
        {
            return GetModel(strWhere, strColumnList, strPrimaryKey, blOrderType);
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="strOrderColumn">以逗号分隔的排序字段名称</param>
        /// <param name="blOrderType">排序类型 true=降序 false=升序</param>
        /// <returns></returns>
        public virtual TType GetModel(string strWhere, string strColumnList, string strOrderColumn, bool blOrderType)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("SELECT TOP 1 {0} FROM {1}", string.IsNullOrEmpty(strColumnList) ? BaseAccessHelper.GetSQLFildList(strColumnList) : "*", strTableName);
            if (!string.IsNullOrEmpty(strWhere))
                sbSql.AppendFormat(" WHERE {0}", strWhere);
            if (!string.IsNullOrEmpty(strOrderColumn))
                sbSql.AppendFormat(" ORDER BY {0} {1}", strOrderColumn, blOrderType ? "DESC" : "ASC");

            TType model = default(TType);
            Database db = DatabaseFactory.CreateDatabase(strConName);
            DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString());
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr.Read())
                    model = GetModel(dr);
            }
            return model;
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="dr">一个数据行</param>
        /// <returns></returns>
        public virtual TType GetModel(DataRow dr)
        {
            TType model = new TType();
            PropertyInfo[] pis = t.GetPropertyInfo();

            foreach (PropertyInfo pi in pis)
            {
                if (!pi.CanWrite)
                    continue;

                try
                {
                    if (dr[pi.Name].ToString() != "")
                        pi.SetValue(model, dr[pi.Name], null);
                }
                catch (ArgumentException)
                { continue; }
            }
            return model;
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="drv">一个数据行视图</param>
        public virtual TType GetModel(DataRowView drv)
        {
            TType model = new TType();
            PropertyInfo[] pis = t.GetPropertyInfo();

            foreach (PropertyInfo pi in pis)
            {
                if (!pi.CanWrite)
                    continue;

                try
                {
                    if (drv[pi.Name].ToString() != "")
                        pi.SetValue(model, drv[pi.Name], null);
                    
                }
                catch (ArgumentException)
                { continue; }
            }
            return model;
        }

        /// <summary>
        /// 获得一个Model对象实例
        /// </summary>
        /// <param name="dr">一个IDataReader</param>
        /// <returns></returns>
        public virtual TType GetModel(IDataReader dr)
        {
            TType model = new TType();
            PropertyInfo[] pis = t.GetPropertyInfo();

            int intIndex;

            foreach (PropertyInfo pi in pis)
            {
                if (!pi.CanWrite)
                    continue;

                try
                {
                    intIndex = dr.GetOrdinal(pi.Name);
                }
                catch (IndexOutOfRangeException)
                { continue; }
                if (dr.IsDBNull(intIndex))
                    continue;
                pi.SetValue(model, dr.GetValue(intIndex), null);
            }
            return model;
        }
        #endregion GetModel

        #region  GetList


        /// <summary>
        /// 获得数据集（数据集上限为500条数据）
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual List<TType> GetListByQuery(string strSql)
        {
            List<TType> list = null;
            DataTable dt = SYSequenceDAL.GetDataTableByQuery(t.DataBaseName, strSql);
            list = GetList(dt);
            return list;
        }

        /// <summary>
        /// 获得数据集（数据集上限为500条数据）
        /// </summary>
        /// <returns></returns>
        public virtual List<TType> GetList()
        {
            return GetList("");
        }

        /// <summary>
        /// 获得数据集（数据集上限为500条数据）
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual List<TType> GetList(string strWhere)
        {
            return GetList(strWhere, 500, 1);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        public virtual List<TType> GetList(string strWhere, int intPageSize, int intPageIndex)
        {
            return GetList(strWhere, intPageSize, intPageIndex, "*");
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <returns></returns>
        public virtual List<TType> GetList(string strWhere, int intPageSize, int intPageIndex, string strColumnList)
        {
            return GetList(strWhere, intPageSize, intPageIndex, strColumnList, true);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <returns></returns>
        public virtual List<TType> GetList(string strWhere, int intPageSize, int intPageIndex, string strColumnList, bool blOrderType)
        {
            return GetList(strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strPrimaryKey);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        public virtual List<TType> GetList(string strWhere, int intPageSize, int intPageIndex, bool blOrderType)
        {
            return GetList(strWhere, intPageSize, intPageIndex, blOrderType, strPrimaryKey);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strOrderColumn">以逗号分隔的排序字段名称</param>
        /// <returns></returns>
        public virtual List<TType> GetList(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strOrderColumn)
        {
            return GetList(strWhere, intPageSize, intPageIndex, blOrderType, "*", strOrderColumn);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="strOrderColumn">以逗号分隔的排序字段名称</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <returns></returns>
        public virtual List<TType> GetList(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList, string strOrderColumn)
        {
            return GetList(strWhere, intPageSize, intPageIndex, strColumnList, strOrderColumn, blOrderType, strTableName);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="strOrderColumn">以逗号分隔的排序字段名称</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        internal List<TType> GetList(string strWhere, int intPageSize, int intPageIndex, string strColumnList, string strOrderColumn, bool blOrderType, string strTableName)
        {
            List<TType> list = null;
            DataTable dt = SYSequenceDAL.GetDataTableByPage(t.DataBaseName, strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strOrderColumn, strTableName);
            list = GetList(dt);
            return list;
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="dt">一个数据表</param>
        /// <returns></returns>
        public virtual List<TType> GetList(DataTable dt)
        {
            List<TType> list = new List<TType>();
            foreach (DataRowView drv in dt.DefaultView)
            {
                list.Add(GetModel(drv));
            }
            return list;
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="dr">一个IDataReader</param>
        /// <returns></returns>
        public virtual List<TType> GetList(IDataReader dr)
        {
            List<TType> list = new List<TType>();
            while (dr.Read())
            {
                list.Add(GetModel(dr));
            }
            return list;
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="dv">一个数据视图</param>
        /// <returns></returns>
        public virtual List<TType> GetList(DataView dv)
        {
            List<TType> list = new List<TType>();
            foreach (DataRowView drv in dv)
            {
                list.Add(GetModel(drv));
            }
            return list;
        }

        /// <summary>
        /// 返回TType列表。
        /// </summary>
        public virtual List<TType> GetList(SearchQuery query)
        {
            List<TType> list = null;
            DataTable dt = GetDataTable(query);
            list = GetList(dt);
            return list;
        }


        /// <summary>
        /// 返回TType列表。
        /// </summary>
        public virtual SearchResult<TType> GetResult(SearchQuery query)
        {
            SearchResult<TType> list = new SearchResult<TType>(query);
            if (string.IsNullOrEmpty(query.StrTableName))
                query.StrTableName = "[" + typeof(TType).Name + "]";

            string strSQL = string.Format("SELECT COUNT(1) FROM {0} {1};", query.StrTableName, "{0}");
            if (!string.IsNullOrEmpty(query.StrFilter))
                strSQL = string.Format(strSQL, string.Format(" WHERE {0}", query.StrFilter));
            else
                strSQL = string.Format(strSQL, string.Empty);

            DataSet ds = SYSequenceDAL.ExecuteDataSet(t.DataBaseName, strSQL + query.GetQuery, CommandType.Text, null);
            list.Records = GetList(ds.Tables[1]);
            list.TotalRecords = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            return list;
        }
        #endregion  GetList

        /// <summary>
        /// 获得数据集
        /// </summary>
        public DataSet GetDataSet(SearchQuery query)
        {
            if (string.IsNullOrEmpty(query.StrTableName))
                query.StrTableName = "[" + typeof(TType).Name + "]";

            return SYSequenceDAL.ExecuteDataSet(t.DataBaseName, query.GetQuery, CommandType.Text, null);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        public DataTable GetDataTable(SearchQuery query)
        {
            return GetDataSet(query).Tables[0];
        }

        #region  GetDataTable
        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <returns></returns>
        public virtual DataTable GetDataTable()
        {
            return GetDataTable("");
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual DataTable GetDataTable(string strWhere)
        {
            return GetDataTable(strWhere, 500, 1);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        public virtual DataTable GetDataTable(string strWhere, int intPageSize, int intPageIndex)
        {
            return GetDataTable(strWhere, intPageSize, intPageIndex, true);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        public virtual DataTable GetDataTable(string strWhere, int intPageSize, int intPageIndex, bool blOrderType)
        {
            return GetDataTable(strWhere, intPageSize, intPageIndex, blOrderType, "*");
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        public virtual DataTable GetDataTable(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList)
        {
            return GetDataTable(strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strPrimaryKey);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="strOrderColumn">排序字段</param>
        public virtual DataTable GetDataTable(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList, string strOrderColumn)
        {
            return GetDataTable(strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strOrderColumn, strTableName);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="strOrderColumn">排序字段</param>
        /// <param name="sTableName">表名</param>
        public virtual DataTable GetDataTable(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList, string strOrderColumn, string sTableName)
        {
            return SYSequenceDAL.GetDataTableByPage(t.DataBaseName, strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strOrderColumn, sTableName);
        }
        #endregion GetDataTable

        #region  GetReader
        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <returns></returns>
        public IDataReader GetReader()
        {
            return GetReader("");
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        public virtual IDataReader GetReader(string strWhere)
        {
            return GetReader(strWhere, 500, 1);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        public virtual IDataReader GetReader(string strWhere, int intPageSize, int intPageIndex)
        {
            return GetReader(strWhere, intPageSize, intPageIndex, true);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        public virtual IDataReader GetReader(string strWhere, int intPageSize, int intPageIndex, bool blOrderType)
        {
            return GetReader(strWhere, intPageSize, intPageIndex, blOrderType, "*");
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        public virtual IDataReader GetReader(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList)
        {
            return GetReader(strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strPrimaryKey);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="strWhere">Where子句</param>
        /// <param name="intPageSize">每页显示记录数</param>
        /// <param name="intPageIndex">当前页码</param>
        /// <param name="blOrderType">排序规则(true-降序；flase-升序)</param>
        /// <param name="strColumnList">以逗号分隔的查询字段名称</param>
        /// <param name="strOrderColumn">排序字段</param>
        public virtual IDataReader GetReader(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList, string strOrderColumn)
        {
            return GetReader(strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strOrderColumn, strTableName);
        }

        internal IDataReader GetReader(string strWhere, int intPageSize, int intPageIndex, bool blOrderType, string strColumnList, string strOrderColumn, string sTableName)
        {
            return SYSequenceDAL.GetDataReaderByPage(t.DataBaseName, strWhere, intPageSize, intPageIndex, blOrderType, strColumnList, strOrderColumn, strTableName);
        }
        #endregion  GetReader
    }
}
